package Dao.impl;
import BaseQuery.BusinessQuery;
import BaseQuery.BaseQuery;
import Bean.Business;
import Dao.IBusinessDao;
import Utils.JdbcUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class BusinessDao implements IBusinessDao {
	QueryRunner queryRunner;
	public BusinessDao() {
		queryRunner=JdbcUtils.getQueryRunner();
	}
	public int add(Business business) throws SQLException {
		String sql = "insert into t_business(businessAccount, businessName, businessAddress, businessExplain, businessImg, startPrice, deliveryPrice, remarks) VALUES(?,?,?,?,?,?,?,?)";
		return queryRunner.update(sql, business.getBusinessAccount(),
				business.getBusinessName(),
				business.getBusinessAddress(),
				business.getBusinessExplain(),
				business.getBusinessImg(),
				business.getStartPrice(),
				business.getDeliveryPrice(),
				business.getRemarks());
	}

	public int update(Business business) throws SQLException {
		String sql = "update t_business set businessName=?, businessAddress=?, businessExplain=?, businessImg=?, startPrice=?, deliveryPrice=?, remarks=? where businessId=?";
		return queryRunner.update(sql,
				business.getBusinessName(),
				business.getBusinessAddress(),
				business.getBusinessExplain(),
				business.getBusinessImg(),
				business.getStartPrice(),
				business.getDeliveryPrice(),
				business.getRemarks(),
				business.getBusinessId());

	}

	public int delete(Integer id) throws SQLException {
		String sql = "delete from t_business where businessId=?";
		return queryRunner.update(sql,id);

	}

	public Business getById(Integer id) throws SQLException {
		String sql = "select businessId, businessAccount, businessPassword, businessName, businessAddress, businessExplain, businessImg, startPrice, deliveryPrice, remarks from t_business where businessId=?";
		return queryRunner.query(sql, new BeanHandler<Business>(Business.class), id);

	}

	public List<Business> getAll() throws SQLException {
		String sql = "select businessId, businessAccount, businessPassword, businessName, businessAddress, businessExplain, businessImg, startPrice, deliveryPrice, remarks from t_business";
		return queryRunner.query(sql, new BeanListHandler<Business>(Business.class));

	}

	public int getCount(BaseQuery baseQuery) throws SQLException {
		BusinessQuery query=(BusinessQuery) baseQuery;
		List<Object> params=new ArrayList<Object>();
		String sql="select count(businessId) from t_business where 1=1 ";

		if(query.getBusinessName()!=null) {
			sql+="and businessName like concat('%',?,'%')";
			//这个语句是用来匹配这个问号的
			params.add(query.getBusinessName());
		}
		if(query.getBusinessAddress()!=null) {
			sql+="and businessAddress like concat('%',?,'%')";
			params.add(query.getBusinessAddress());
		}
		System.out.println("查询集合："+sql);
		System.out.println("参数集合："+params);
		return queryRunner.query(sql, new ScalarHandler<Long>(), params.toArray()).intValue();
	}

	public List<Business> getByPage(BaseQuery baseQuery) throws SQLException {
		BusinessQuery query = (BusinessQuery)baseQuery;
		List<Object> params=new ArrayList<Object>();
		String sql = "select businessId, businessAccount, businessPassword, businessName, businessAddress, businessExplain, businessImg, startPrice, deliveryPrice, remarks from t_business" +
				" where 1=1 ";
		//动态查询sql
		if(query.getBusinessName()!=null) {
			sql += "and businessName like concat('%',?,'%')";
			params.add(query.getBusinessName());
		}
		if(query.getBusinessAddress()!=null) {
			sql+="and businessAddress like concat ('%',?,'%')";
			params.add(query.getBusinessAddress());
		}
		sql += " ORDER BY businessId DESC limit ?,?";
		params.add(query.getstartIndex());
		//每页显示的条数
		params.add(query.getPageCount());
		System.out.println("查询总记录："+sql);
		System.out.println("参数集合："+params);
		return queryRunner.query(sql,new BeanListHandler<Business>(Business.class),params.toArray());
	}
}
